<?php

namespace Home\Service;

use Home\Common\FIdConst;

/**
 * 数据库升级Service
 *
 * @author 李静波
 */
class UpdateDBService extends PSIBaseService {

	public function updateDatabase() {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$db = M();
		
		// 检查t_psi_db_version是否存在
		if (! $this->tableExists($db, "t_psi_db_version")) {
			return $this->bad("表t_psi_db_db_version不存在，数据库结构实在是太久远了，无法升级");
		}
		
		// 检查t_psi_db_version中的版本号
		$sql = "select db_version from t_psi_db_version";
		$data = $db->query($sql);
		$dbVersion = $data[0]["db_version"];
		if ($dbVersion == $this->CURRENT_DB_VERSION) {
			return $this->bad("当前数据库是最新版本，不用升级");
		}
		
		$this->update_20160708_01($db);
		$this->update_20160710_01($db);
		$this->update_20160711_01($db);
		$this->update_20160712_01($db);
		$this->update_20160712_02($db);
		$this->update_20160714_01($db);
		$this->update_20160715_01($db);
		$this->update_20160715_02($db);
		$this->update_20160715_03($db);
		$this->update_20160719_01($db);
		$this->update_20160719_02($db);
		$this->update_20160720_01($db);
		$this->update_20160721_01($db);
		$this->update_20160722_01($db);
		$this->update_20160723_01($db);
		$this->update_20160723_02($db);
		$this->update_20160725_01($db);
		$this->update_20160726_01($db);
		$this->update_20160726_02($db);
		$this->update_20160727_01($db);
		$this->update_20160728_01($db);
		$this->update_20160729_01($db);
		$this->update_20160729_02($db);
		$this->update_20160801_01($db);
		$this->update_20160801_02($db);
		$this->update_20160801_03($db);
		$this->update_20160801_04($db);
		$this->update_20160803_01($db);
		$this->update_20160803_02($db);
		$this->update_20160804_01($db);
		
		$sql = "delete from t_psi_db_version";
		$db->execute($sql);
		$sql = "insert into t_psi_db_version (db_version, update_dt) 
				values ('%s', now())";
		$db->execute($sql, $this->CURRENT_DB_VERSION);
		
		$bl = new BizlogService();
		$bl->insertBizlog("升级数据库，数据库版本 = " . $this->CURRENT_DB_VERSION);
		
		return $this->ok();
	}

	private function update_20160804_01($db) {
		// 本次更新：新增省、市、县数据字典表
		$talbeName = "t_province";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_province` (
					  `id` varchar(255) NOT NULL,
					  `name` varchar(255) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
					";
			$db->execute($sql);
		}
		
		$talbeName = "t_city";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_city` (
					  `id` varchar(255) NOT NULL,
					  `province_id` varchar(255) NOT NULL,
					  `name` varchar(255) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
					";
			$db->execute($sql);
		}
		
		$talbeName = "t_district";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_district` (
					  `id` varchar(255) NOT NULL,
					  `city_id` varchar(255) NOT NULL,
					  `name` varchar(255) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
					";
			$db->execute($sql);
		}
	}

	private function update_20160803_02($db) {
		// 本次更新：t_it_bill_detail新增qc_begin_dt qc_end_dt expiration字段
		$tableName = "t_it_bill_detail";
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160803_01($db) {
		// 本次更新：t_so_bill新增字段 print_count, last_print_dt
		$tableName = "t_so_bill";
		$columnName = "print_count";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int(11) not null default 0;";
			$db->execute($sql);
		}
		
		$columnName = "last_print_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
	}

	private function update_20160801_04($db) {
		// 本次更新：新增模块 快递公司
		$fid = FIdConst::EXPRESS_COMPANY;
		$name = "快递公司";
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售订单";
			$note = "通过菜单进入快递公司模块的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		$sql = "select count(*) as cnt from t_menu_item
				where id = '0908' ";
		$data = $db->query($sql);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_menu_item(id, caption, fid, parent_id, show_order)
					values ('0908', '%s', '%s', '09', 8)";
			$db->execute($sql, $name, $fid);
		}
	}

	private function update_20160801_03($db) {
		// 本次更新：新增表t_express_comnapy;
		// t_so_bill新增字段sales_mode_id, express_company_id, freight
		// t_so_bill_detail新增字段 fregith
		//
		$talbeName = "t_express_company";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_express_company` (
					  `id` varchar(255) NOT NULL,
					  `name` varchar(255) NOT NULL,
					  `py` varchar(255) NOT NULL,
					  `data_org` varchar(255) DEFAULT NULL,
					  `company_id` varchar(255) DEFAULT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
					";
			$db->execute($sql);
		}
		
		$tableName = "t_so_bill";
		$columnName = "sales_mode_id";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		$columnName = "express_company_id";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default 0;";
			$db->execute($sql);
		}
		
		$tableName = "t_so_bill_detail";
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default 0;";
			$db->execute($sql);
		}
	}

	private function update_20160801_02($db) {
		// 本次更新： 新增销售收入方式模块
		$fid = FIdConst::SALES_MODE;
		$name = "销售收入方式";
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售订单";
			$note = "通过菜单进入销售收入方式模块的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		$sql = "select count(*) as cnt from t_menu_item
				where id = '0907' ";
		$data = $db->query($sql);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_menu_item(id, caption, fid, parent_id, show_order)
					values ('0907', '%s', '%s', '09', 7)";
			$db->execute($sql, $name, $fid);
		}
	}

	private function update_20160801_01($db) {
		// 本次更新：新增表t_sales_mode
		$talbeName = "t_sales_mode";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_sales_mode` (
					  `id` varchar(255) NOT NULL,
					  `mode_type` int(11) NOT NULL,
					  `name` varchar(255) NOT NULL,
					  `data_org` varchar(255) DEFAULT NULL,
					  `company_id` varchar(255) DEFAULT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
					";
			$db->execute($sql);
		}
	}

	private function update_20160729_02($db) {
		// 本次更新：t_ic_bill新增字段bill_type
		$tableName = "t_ic_bill";
		$columnName = "bill_type";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int(11) not null default 0;";
			$db->execute($sql);
		}
	}

	private function update_20160729_01($db) {
		// 本次更新：盘点单新增保质期
		$tableName = "t_ic_bill_detail";
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160728_01($db) {
		// 本次更新：销售出库单权限细化到按钮
		
		// 新建销售出库单
		$fid = FIdConst::WAREHOUSING_SALE_ADD;
		$name = "销售出库 - 新建销售出库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售出库";
			$note = "销售出库[新建销售出库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 编辑销售出库单
		$fid = FIdConst::WAREHOUSING_SALE_EDIT;
		$name = "销售出库 - 编辑销售出库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售出库";
			$note = "销售出库[编辑销售出库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 删除销售出库单
		$fid = FIdConst::WAREHOUSING_SALE_DELETE;
		$name = "销售出库 - 删除销售出库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售出库";
			$note = "销售出库[删除销售出库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 提交销售出库单
		$fid = FIdConst::WAREHOUSING_SALE_COMMIT;
		$name = "销售出库 - 提交销售出库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售出库";
			$note = "销售出库[提交销售出库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 生成PDF
		$fid = FIdConst::WAREHOUSING_SALE_PDF;
		$name = "销售出库 - 生成PDF";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "销售出库";
			$note = "销售出库[生成PDF]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
	}

	private function update_20160727_01($db) {
		// 本次更新：t_so_bill新增字段 warehouse_id
		$tableName = "t_so_bill";
		$columnName = "warehouse_id";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160726_02($db) {
		// 本次更新：新增表t_customer_saleprice
		$talbeName = "t_customer_saleprice";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_customer_saleprice` (
					  `id` varchar(255) NOT NULL,
					  `customer_id` varchar(255) NOT NULL,
					  `goods_id` varchar(255) NOT NULL,
					  `sale_price` decimal(19,2) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
					";
			$db->execute($sql);
		}
	}

	private function update_20160726_01($db) {
		// 本次更新：t_so_bill_detail新增字段 qc_begin_dt、qc_end_dt、expiration
		$tableName = "t_so_bill_detail";
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160725_01($db) {
		// 本次更新：t_goods表新增字段 disabled
		$tableName = "t_goods";
		$columnName = "disabled";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int(11) default 0;";
			$db->execute($sql);
		}
	}

	private function update_20160723_02($db) {
		// 本次更新：销售出库单新增保质期字段
		$tableName = "t_ws_bill_detail";
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160723_01($db) {
		// 本次更新：采购退货出库单新增保质期字段
		$tableName = "t_pr_bill_detail";
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160722_01($db) {
		// 本次更新：新增表t_inventory_lot
		$tableName = "t_inventory_lot";
		
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_inventory_lot` (
					  `id` bigint(20) NOT NULL AUTO_INCREMENT,
					  `balance_count` decimal(19,2) NOT NULL,
					  `balance_money` decimal(19,2) NOT NULL,
					  `balance_price` decimal(19,2) NOT NULL,
					  `goods_id` varchar(255) NOT NULL,
					  `in_count` decimal(19,2) DEFAULT NULL,
					  `in_money` decimal(19,2) DEFAULT NULL,
					  `in_price` decimal(19,2) DEFAULT NULL,
					  `out_count` decimal(19,2) DEFAULT NULL,
					  `out_money` decimal(19,2) DEFAULT NULL,
					  `out_price` decimal(19,2) DEFAULT NULL,
					  `afloat_count` decimal(19,2) DEFAULT NULL,
					  `afloat_money` decimal(19,2) DEFAULT NULL,
					  `afloat_price` decimal(19,2) DEFAULT NULL,
					  `warehouse_id` varchar(255) NOT NULL,
					  `data_org` varchar(255) DEFAULT NULL,
					  `company_id` varchar(255) DEFAULT NULL,
					  `begin_dt` datetime NOT NULL,
					  `end_dt` datetime NOT NULL,
					  `expiration` int(11) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
					";
			$db->execute($sql);
		}
	}

	private function update_20160721_01($db) {
		// 本次更新：采购入库权限细化到按钮
		
		// 新建采购入库单
		$fid = FIdConst::PURCHASE_WAREHOUSE_ADD;
		$name = "采购入库 - 新建采购入库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "采购入库";
			$note = "采购入库[新建采购入库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 编辑采购入库单
		$fid = FIdConst::PURCHASE_WAREHOUSE_EDIT;
		$name = "采购入库 - 编辑采购入库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "采购入库";
			$note = "采购入库[编辑采购入库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 删除采购入库单
		$fid = FIdConst::PURCHASE_WAREHOUSE_DELETE;
		$name = "采购入库 - 删除采购入库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "采购入库";
			$note = "采购入库[删除采购入库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		// 提交采购入库单
		$fid = FIdConst::PURCHASE_WAREHOUSE_COMMIT;
		$name = "采购入库 - 提交采购入库单";
		
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "采购入库";
			$note = "采购入库[提交采购入库单]按钮的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
	}

	private function update_20160720_01($db) {
		// 本次更新：采购入库单新增 运费、保质期
		$tableName = "t_pw_bill";
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default null;";
			$db->execute($sql);
		}
		
		// 明细表
		$tableName = "t_pw_bill_detail";
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160719_02($db) {
		// 本次更新：采购订单新增 运费、保质期
		$tableName = "t_po_bill";
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default null;";
			$db->execute($sql);
		}
		
		// 明细表
		$tableName = "t_po_bill_detail";
		$columnName = "freight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19, 2) default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_begin_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "qc_end_dt";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} datetime default null;";
			$db->execute($sql);
		}
		
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int default null;";
			$db->execute($sql);
		}
	}

	private function update_20160719_01($db) {
		// 本次更新：新增表t_inventory_detail_lot
		$tableName = "t_inventory_detail_lot";
		
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_inventory_detail_lot` (
					  `id` bigint(20) NOT NULL AUTO_INCREMENT,
					  `balance_count` decimal(19,2) NOT NULL,
					  `balance_money` decimal(19,2) NOT NULL,
					  `balance_price` decimal(19,2) NOT NULL,
					  `biz_date` datetime NOT NULL,
					  `biz_user_id` varchar(255) NOT NULL,
					  `date_created` datetime DEFAULT NULL,
					  `goods_id` varchar(255) NOT NULL,
					  `in_count` decimal(19,2) DEFAULT NULL,
					  `in_money` decimal(19,2) DEFAULT NULL,
					  `in_price` decimal(19,2) DEFAULT NULL,
					  `out_count` decimal(19,2) DEFAULT NULL,
					  `out_money` decimal(19,2) DEFAULT NULL,
					  `out_price` decimal(19,2) DEFAULT NULL,
					  `ref_number` varchar(255) DEFAULT NULL,
					  `ref_type` varchar(255) NOT NULL,
					  `warehouse_id` varchar(255) NOT NULL,
					  `data_org` varchar(255) DEFAULT NULL,
					  `company_id` varchar(255) DEFAULT NULL,
					  `begin_dt` datetime NOT NULL,
					  `end_dt` datetime NOT NULL,
					  `expiration` int(11) NOT NULL,
					  PRIMARY KEY (`id`)
					) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
					";
			$db->execute($sql);
		}
	}

	private function update_20160715_03($db) {
		// 本次更新 t_supplier新增字段province、city、district、postcode、email01、email02
		$tableName = "t_supplier";
		$columnName = "province";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "city";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "district";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "postcode";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "email01";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "email02";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160715_02($db) {
		// 本次更新： t_customer表新增字段 email01、email02
		$tableName = "t_customer";
		$columnName = "email01";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "email02";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160715_01($db) {
		// 本次更新：t_customer表新增字段 taobao_nickname、province、city、district、postcode
		$tableName = "t_customer";
		$columnName = "taobao_nickname";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "province";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "city";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "district";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		$columnName = "postcode";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160714_01($db) {
		// 本次更新：t_goods表新增字段 expiration、weight、manufacturer
		$tableName = "t_goods";
		$columnName = "expiration";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} int(11) default null;";
			$db->execute($sql);
		}
		
		$columnName = "weight";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19,2) default null;";
			$db->execute($sql);
		}
		
		$columnName = "manufacturer";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160712_02($db) {
		// 本次更新：t_goods表新增字段base_sale_price
		$tableName = "t_goods";
		$columnName = "base_sale_price";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19,2) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160712_01($db) {
		// 本次更新：t_price_system表新增字段 factor
		$tableName = "t_price_system";
		$columnName = "factor";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} decimal(19,2) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160711_01($db) {
		// 本次更新： 新增模块 价格体系
		$fid = FIdConst::PRICE_SYSTEM;
		$name = "价格体系";
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "商品";
			$note = "通过菜单进入价格体系模块的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		$sql = "select count(*) as cnt from t_menu_item
				where id = '090104' ";
		$data = $db->query($sql);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_menu_item(id, caption, fid, parent_id, show_order)
					values ('090104', '%s', '%s', '0901', 4)";
			$db->execute($sql, $name, $fid);
		}
	}

	private function update_20160710_01($db) {
		// 本次更新： 价格体系表结构调整
		
		// 新增表 t_price_system
		$tableName = "t_price_system";
		
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_price_system` (
						  `id` varchar(255) NOT NULL,
						  `name` varchar(255) NOT NULL,
						  `data_org` varchar(255) DEFAULT NULL,
						  `company_id` varchar(255) DEFAULT NULL,
						  PRIMARY KEY (`id`)
						) ENGINE=InnoDB DEFAULT CHARSET=utf8;
					";
			$db->execute($sql);
		}
		
		// 新增表 t_goods_price
		$tableName = "t_goods_price";
		if (! $this->tableExists($db, $tableName)) {
			$sql = "CREATE TABLE IF NOT EXISTS `t_goods_price` (
						  `id` varchar(255) NOT NULL,
						  `goods_id` varchar(255) NOT NULL,
						  `ps_id` varchar(255) NOT NULL,
						  `price` decimal(19,2) NOT NULL,
						  `data_org` varchar(255) DEFAULT NULL,
						  `company_id` varchar(255) DEFAULT NULL,
						  PRIMARY KEY (`id`)
						) ENGINE=InnoDB DEFAULT CHARSET=utf8;
					";
			$db->execute($sql);
		}
		
		// t_customer 表新增字段 ps_id
		$tableName = "t_customer";
		$columnName = "ps_id";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
		
		// t_customer_category 新增字段 ps_id
		$tableName = "t_customer_category";
		$columnName = "ps_id";
		if (! $this->columnExists($db, $tableName, $columnName)) {
			$sql = "alter table {$tableName} add {$columnName} varchar(255) default null;";
			$db->execute($sql);
		}
	}

	private function update_20160708_01($db) {
		// 本次更新： 新增模块【淘宝店铺管理】
		$fid = FIdConst::TAOBAO_SHOP;
		$name = "淘宝店铺管理";
		$sql = "select count(*) as cnt from t_fid where fid = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_fid(fid, name) value('%s', '%s')";
			$db->execute($sql, $fid, $name);
		}
		
		$sql = "select count(*) as cnt from t_permission where id = '%s' ";
		$data = $db->query($sql, $fid);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$category = "淘宝店宝贝管理";
			$note = "通过菜单进入淘宝店铺管理模块的权限";
			$ps = new PinyinService();
			$py = $ps->toPY($name);
			$sql = "insert into t_permission(id, fid, name, note, category, py)
					value('%s', '%s', '%s', '%s', '%s', '%s')";
			$db->execute($sql, $fid, $fid, $name, $note, $category, $py);
		}
		
		$sql = "select count(*) as cnt from t_menu_item
				where id = '090602' ";
		$data = $db->query($sql);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$sql = "insert into t_menu_item(id, caption, fid, parent_id, show_order)
					values ('090602', '%s', '%s', '0906', 2)";
			$db->execute($sql, $name, $fid);
		}
	}
}